{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "pd.set_option('max_columns', 4, 'max_rows', 10, 'max_colwidth', 12)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "fname = ['Paul', 'John', 'Richard', 'George']\n",
    "lname = ['McCartney', 'Lennon', 'Starkey', 'Harrison']\n",
    "birth = [1942, 1940, 1940, 1943]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "people = {'first': fname, 'last': lname, 'birth': birth}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles = pd.DataFrame(people)\n",
    "beatles"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(people, index=['a', 'b', 'c', 'd'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s More"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.DataFrame(\n",
    "[{\"first\":\"Paul\",\"last\":\"McCartney\", \"birth\":1942},\n",
    " {\"first\":\"John\",\"last\":\"Lennon\", \"birth\":1940},\n",
    " {\"first\":\"Richard\",\"last\":\"Starkey\", \"birth\":1940},\n",
    " {\"first\":\"George\",\"last\":\"Harrison\", \"birth\":1943}])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "[{\"first\":\"Paul\",\"last\":\"McCartney\", \"birth\":1942},\n",
    " {\"first\":\"John\",\"last\":\"Lennon\", \"birth\":1940},\n",
    " {\"first\":\"Richard\",\"last\":\"Starkey\", \"birth\":1940},\n",
    " {\"first\":\"George\",\"last\":\"Harrison\", \"birth\":1943}],\n",
    " columns=['last', 'first', 'birth'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "from io import StringIO\n",
    "fout = StringIO()\n",
    "beatles.to_csv(fout)  # use a filename instead of fout"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "print(fout.getvalue())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s More"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "_ = fout.seek(0)\n",
    "pd.read_csv(fout)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "_ = fout.seek(0)\n",
    "pd.read_csv(fout, index_col=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "fout = StringIO()\n",
    "beatles.to_csv(fout, index=False) \n",
    "print(fout.getvalue())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds = pd.read_csv('data/diamonds.csv', nrows=1000)\n",
    "diamonds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2 = pd.read_csv('data/diamonds.csv', nrows=1000,\n",
    "    dtype={'carat': np.float32, 'depth': np.float32,\n",
    "           'table': np.float32, 'x': np.float32,\n",
    "           'y': np.float32, 'z': np.float32,\n",
    "           'price': np.int16})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2.cut.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2.color.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds2.clarity.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds3 = pd.read_csv('data/diamonds.csv', nrows=1000,\n",
    "    dtype={'carat': np.float32, 'depth': np.float32,\n",
    "           'table': np.float32, 'x': np.float32,\n",
    "           'y': np.float32, 'z': np.float32,\n",
    "           'price': np.int16,\n",
    "           'cut': 'category', 'color': 'category',\n",
    "           'clarity': 'category'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds3.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "np.iinfo(np.int8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "np.finfo(np.float16)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "cols = ['carat', 'cut', 'color', 'clarity', 'depth', 'table', 'price']\n",
    "diamonds4 = pd.read_csv('data/diamonds.csv', nrows=1000,\n",
    "    dtype={'carat': np.float32, 'depth': np.float32,\n",
    "           'table': np.float32, 'price': np.int16,\n",
    "           'cut': 'category', 'color': 'category',\n",
    "           'clarity': 'category'},\n",
    "    usecols=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds4.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cols = ['carat', 'cut', 'color', 'clarity', 'depth', 'table', 'price']\n",
    "diamonds_iter = pd.read_csv('data/diamonds.csv', nrows=1000,\n",
    "    dtype={'carat': np.float32, 'depth': np.float32,\n",
    "           'table': np.float32, 'price': np.int16,\n",
    "           'cut': 'category', 'color': 'category',\n",
    "           'clarity': 'category'},\n",
    "    usecols=cols,\n",
    "    chunksize=200)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def process(df):\n",
    "    return f'processed {df.size} items'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "for chunk in diamonds_iter:\n",
    "    process(chunk)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s more \\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.price.memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.price.memory_usage(index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.cut.memory_usage()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds.cut.memory_usage(deep=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds4.to_feather('/tmp/d.arr')\n",
    "diamonds5 = pd.read_feather('/tmp/d.arr')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "diamonds4.to_parquet('/tmp/d.pqt')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles.to_excel('/tmp/beat.xls')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles.to_excel('/tmp/beat.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beat2 = pd.read_excel('/tmp/beat.xls')\n",
    "beat2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beat2 = pd.read_excel('/tmp/beat.xls', index_col=0)\n",
    "beat2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beat2.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s more\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "xl_writer = pd.ExcelWriter('/tmp/beat.xlsx')\n",
    "beatles.to_excel(xl_writer, sheet_name='All')\n",
    "beatles[beatles.birth < 1941].to_excel(xl_writer, sheet_name='1940')\n",
    "xl_writer.save()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "autos = pd.read_csv('data/vehicles.csv.zip')\n",
    "autos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "autos.modifiedOn.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "autos.modifiedOn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.to_datetime(autos.modifiedOn)  # doctest: +SKIP"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "autos = pd.read_csv('data/vehicles.csv.zip',\n",
    "    parse_dates=['modifiedOn'])  # doctest: +SKIP\n",
    "autos.modifiedOn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import zipfile"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "with zipfile.ZipFile('data/kaggle-survey-2018.zip') as z:\n",
    "    print('\\n'.join(z.namelist()))\n",
    "    kag = pd.read_csv(z.open('multipleChoiceResponses.csv'))\n",
    "    kag_questions = kag.iloc[0]\n",
    "    survey = kag.iloc[1:]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "print(survey.head(2).T)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s more\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "con = sqlite3.connect('data/beat.db')\n",
    "with con:\n",
    "    cur = con.cursor()\n",
    "    cur.execute(\"\"\"DROP TABLE Band\"\"\")\n",
    "    cur.execute(\"\"\"CREATE TABLE Band(id INTEGER PRIMARY KEY,\n",
    "        fname TEXT, lname TEXT, birthyear INT)\"\"\")\n",
    "    cur.execute(\"\"\"INSERT INTO Band VALUES(\n",
    "        0, 'Paul', 'McCartney', 1942)\"\"\")\n",
    "    cur.execute(\"\"\"INSERT INTO Band VALUES(\n",
    "        1, 'John', 'Lennon', 1940)\"\"\")\n",
    "    _ = con.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import sqlalchemy as sa\n",
    "engine = sa.create_engine(\n",
    "  'sqlite:///data/beat.db', echo=True)\n",
    "sa_connection = engine.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beat = pd.read_sql('Band', sa_connection, index_col='id')\n",
    "beat"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "sql = '''SELECT fname, birthyear from Band'''\n",
    "fnames = pd.read_sql(sql, con)\n",
    "fnames"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it work\\'s\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "import json\n",
    "encoded = json.dumps(people)\n",
    "encoded"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "json.loads(encoded)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "beatles = pd.read_json(encoded)\n",
    "beatles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "records = beatles.to_json(orient='records')\n",
    "records"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.read_json(records, orient='records')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "split = beatles.to_json(orient='split')\n",
    "split"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.read_json(split, orient='split')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "index = beatles.to_json(orient='index')\n",
    "index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.read_json(index, orient='index')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "values = beatles.to_json(orient='values')\n",
    "values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.read_json(values, orient='values')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "(pd.read_json(values, orient='values')\n",
    "   .rename(columns=dict(enumerate(['first', 'last', 'birth'])))\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "table = beatles.to_json(orient='table')\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "pd.read_json(table, orient='table')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There\\'s more\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "output = beat.to_dict()\n",
    "output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "output['version'] = '0.4.1'\n",
    "json.dumps(output)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How to do it\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "url ='https://en.wikipedia.org/wiki/The_Beatles_discography'\n",
    "dfs = pd.read_html(url)\n",
    "len(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "dfs[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "url ='https://en.wikipedia.org/wiki/The_Beatles_discography'\n",
    "dfs = pd.read_html(url, match='List of studio albums', na_values='—')\n",
    "len(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "dfs[0].columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "url ='https://en.wikipedia.org/wiki/The_Beatles_discography'\n",
    "dfs = pd.read_html(url, match='List of studio albums', na_values='—',\n",
    "    header=[0,1])\n",
    "len(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "dfs[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "dfs[0].columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "df = dfs[0]\n",
    "df.columns = ['Title', 'Release', 'UK', 'AUS', 'CAN', 'FRA', 'GER',\n",
    "    'NOR', 'US', 'Certifications']\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "res = (df\n",
    "  .pipe(lambda df_: df_[~df_.Title.str.startswith('Released')])\n",
    "  .iloc[:-1]\n",
    "  .assign(release_date=lambda df_: pd.to_datetime(\n",
    "             df_.Release.str.extract(r'Released: (.*) Label')\n",
    "               [0]\n",
    "               .str.replace(r'\\[E\\]', '')\n",
    "          ),\n",
    "          label=lambda df_:df_.Release.str.extract(r'Label: (.*)')\n",
    "         )\n",
    "   .loc[:, ['Title', 'UK', 'AUS', 'CAN', 'FRA', 'GER', 'NOR',\n",
    "            'US', 'release_date', 'label']]\n",
    ")\n",
    "res"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### How it works\\..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### There is more\\..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "lines_to_next_cell": 2
   },
   "outputs": [],
   "source": [
    "url = 'https://github.com/mattharrison/datasets/blob/master/data/anscombes.csv'\n",
    "dfs = pd.read_html(url, attrs={'class': 'csv-data'})\n",
    "len(dfs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dfs[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "jupytext": {
   "cell_metadata_filter": "-all",
   "main_language": "python",
   "notebook_metadata_filter": "-all"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
